package dao;

import entity.UserInfo;
import openConn.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserInfoDao {
    public static int add(String userName,String passWord , int userRoot,String userSex) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "insert into user_info(user_name,user_password,user_root,user_sex) values (?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userName);
        statement.setString(2,passWord);
        statement.setInt(3,userRoot);
        statement.setString(4,userSex);
        int count = statement.executeUpdate();
        statement.close();
        connection.close();
        return count;
    }



    public static List<UserInfo> selectAll() throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "select * from user_info";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        List<UserInfo> userInfoList = new ArrayList<>();
        UserInfo userInfo = null;
        while (resultSet.next()){
            int userID = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            String passWord = resultSet.getString("user_password");
            int root = resultSet.getInt("root");
            String userSex = resultSet.getString("user_sex");
            Date createTime = resultSet.getTime("create_time");
            Date updateTime = resultSet.getTime("update_time");
            int delStatus = resultSet.getInt("del_status");
            userInfo = new UserInfo(userID,userName,passWord,root,userSex,createTime,updateTime,delStatus);
            userInfoList.add(userInfo);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return userInfoList;
    }

    public static List<UserInfo> selectForUser() throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "select user_id,user_name,user_password,user_sex from user_info";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        List<UserInfo> userInfoList = new ArrayList<>();
        UserInfo userInfo = null;
        while (resultSet.next()){
            int userID = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            String passWord = resultSet.getString("user_password");
            String userSex = resultSet.getString("user_sex");
            userInfo = new UserInfo(userID,userName,passWord,userSex);
            userInfoList.add(userInfo);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return userInfoList;
    }

    public static int selectRoot (String userName,String passWord) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "select user_root from user_info where user_name = ? and user_password = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userName);
        statement.setString(2,passWord);
        ResultSet resultSet = statement.executeQuery();
        int count = 0;
        while (resultSet.next()){
            count = resultSet.getInt("user_root");
        }
        resultSet.close();
        statement.close();
        connection.close();
        return count;
    }
    public static UserInfo Login(UserInfo userInfo) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "select user_name,user_password,del_status from user_info where user_name = ? and user_password = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userInfo.getUserName());
        statement.setString(2,userInfo.getPassWord());
        ResultSet resultSet = statement.executeQuery();
        UserInfo result = null;
        while (resultSet.next()){
            String userName = resultSet.getString("user_name");
            String passWord = resultSet.getString("user_password");
            int delStatus = resultSet.getInt("del_status");
            result = new UserInfo(userName,passWord,delStatus);
        }
        return result;
    }

    public static UserInfo secletInfo(UserInfo userInfo) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "select user_id,user_name,user_password,user_sex,create_time,update_time from user_info where user_name = ? and user_password = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userInfo.getUserName());
        statement.setString(2,userInfo.getPassWord());
        ResultSet resultSet = statement.executeQuery();
        UserInfo result = null;
        while (resultSet.next()){
            int userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            String passWord = resultSet.getString("user_password");
            String userSex = resultSet.getString("user_sex");
            Date createTime= resultSet.getDate("create_time");
            Date updateTime = resultSet.getDate("update_time");
            result = new UserInfo(userId,userName,passWord,userSex,createTime,updateTime);
        }
        return result;
    }

    public static int update (int userId,String userName,String passWord,String userSex) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "update user_info set user_name = ? , user_password = ?, user_sex =? where user_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userName);
        statement.setString(2,passWord);
        statement.setString(3,userSex);
        statement.setInt(4,userId);
        int count = statement.executeUpdate();

        statement.close();
        connection.close();
        System.out.println("修改成功");
        return count;
    }
    public static int delete (int userId,int delStatus) throws SQLException {
        Connection connection = DruidUtils.openConn();
        String sql = "update user_info set del_status = ? where user_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,delStatus);
        statement.setInt(2,userId);
        int count = statement.executeUpdate();

        statement.close();
        connection.close();
        return count;
    }
}
